from string import Template
from tack.lib import string_app, json_app, content_headers, status_codes
from datetime import datetime
from util import build_row_dict, state_def


class Sql:
    select_commitment_by_date = """
    SELECT DISTINCT temp.commitment_id FROM 
    (
    SELECT tc.commitment_id AS commitment_id
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
        LEFT OUTER JOIN tmpo_commitment_leg tcl
            ON tc.commitment_id = tcl.commitment_id
    WHERE CONVERT(CHAR(10),tcl.start,121) = '$working_date'
        AND tcd.state_key = 3
        AND tcd.state_finish IS NULL
    ) AS temp
    """


    select_commitment_legs_by_date = """
    SELECT tcld.driver_id AS driver_id,
           tcl.start AS start,
           tcl.finish AS finish,
           tcl.location AS location,
           tcl.destination AS destination
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
        LEFT OUTER JOIN tmpo_commitment_leg tcl
            ON tc.commitment_id = tcl.commitment_id
        INNER JOIN tmpo_commitment_leg_driver tcld
            ON tcl.commitment_leg_id = tcld.commitment_leg_id
    WHERE CONVERT(CHAR(10),tcl.start,121) = '$working_date'
        AND tcd.state_key = 3
        AND tcd.state_finish IS NULL
    ORDER BY tcl.start
    """


    select_du_by_date = """
    SELECT du.driver_id AS driver_id,
           du.du_start AS start,
           du.du_end AS finish
    FROM tmpo_driver_unavailable du
    WHERE CONVERT(CHAR(10),du.du_start,121) = '$working_date'
    ORDER BY du.du_start
    """


def get_based_on_date_range(environ, start_response):
    try:
        working_date = environ['dispatcher.groups']['date']
        throwaway = datetime.strptime(working_date,'%Y-%m-%d')
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    output = []
    sql = Sql.select_commitment_legs_by_date
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        item = {}
        item['type'] = 'commitment'
        item['date'] = working_date
        d = build_row_dict(cursor.description, row)
        item['driver_id'] = d['driver_id']
        desc = d['location'] + ', ' + d['start'][11:13] + d['start'][14:16]
        desc += '-' + d['finish'][11:13] + d['finish'][14:16]
        item['description'] = desc
        output.append(item)
    sql = Sql.select_du_by_date
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        item = {}
        item['type'] = 'unavailable'
        item['date'] = working_date
        d = build_row_dict(cursor.description, row)
        item['driver_id'] = d['driver_id']
        desc = d['start'][11:13] + d['start'][14:16] + '-'
        desc += d['finish'][11:13] + d['finish'][14:16]
        item['description'] = desc
        output.append(item)
    return json_app(output)(environ, start_response)